【新機能】Redshift ALTER TABLE APPENDによるデータ移動を試してみました

【新機能】Redshift ALTER TABLE APPENDによるデータ移動を試してみました

#Amazon Redshift
#AWS
石川覚

石川覚

facebook logohatena logotwitter logo
Clock Icon2016.02.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

ALTER TABLE APPENDによるデータ移動が利用できるようになりました。（Cluster Version: 1.0.1034）他のRDBでは聞いたことのない "ALTER TABLE APPEND" ですが、この機能はデータの物理的な移動を行わずにテーブル間でのデータ移動を実現することで高速になるというメリットがあります。どういった用途に適した機能であるかご紹介します。

ALTER TABLE APPENDによるデータ移動が追加 (2016/02/09)

ALTER TABLE APPEND とは

この新しいALTER TABLE APPENDは、ある表（ソーステーブル）のデータ全体を別の表（ターゲットテーブル）にデータを「移動（MOVE）」する機能です。「移動」ですので、ソーステーブルは空（から）になります。つまり、従来はINSERT INTO SELECT と、ソーステーブルのデータ削除（DELETE or TRUNCATE）が必要でしたが、ALTER TABLE APPEND ではデータの物理的な移動を行わずにテーブル間のデータ移動を実現しています。そのため実行速度が高速になる一方でソーステーブルからはデータが消えるというわけです。

詳細は、Redshift公式マニュアル（英語）ALTER TABLE APPEND を参照してください。

ALTER TABLE APPEND の構文

ALTER TABLE target_table_name APPEND FROM source_table_name 
[ IGNOREEXTRA | FILLTARGET ]

ALTER TABLE APPEND の使用上の注意

ソーステーブルとターゲットテーブルのカラム定義に違いがあった場合はどうなるのでしょうか？この場合は ALTER TABLE APPENDにIGNOREEXTRA か FILLTARGET のどちらかのオプションを指定する必要があります。

  • IGNOREEXTRA：ソーステーブルにある列がターゲットテーブルに存在しない場合、その列は無視されます（捨てられます）
  • FILLTARGET：ターゲットテーブルに、ソースには無い列が存在する場合、その列にはデフォルト値が適用されます。デフォルトが定義されていないケースではNULLが入ります
  • ソーステーブルとターゲットテーブルの両方が余分な列が含まれている場合、コマンドは失敗します。FILLTARGETとIGNOREEXTRAの両方を使用することはできません。

なお、ALTER TABLE APPEND はDDLであり、トランザクション対応では無いのでROLLBACKできません。

同じ名前で異なる属性を持つカラムが両方のテーブルに存在する場合、コマンドは失敗します。同じ名前の列は、以下の共通の属性を持っている必要があります。

  • Data type
  • Column size
  • Compression encoding
  • Not null
  • Sort style
  • Sort key columns
  • Distribution style
  • Distribution key columns

実行例

以下の例では、sales_monthly （ソーステーブル）のデータ全体をsales（ターゲットテーブル）にデータを移動します。

alter table sales append from sales_monthly;

動作検証

ALTER TABLE APPEND は内部的にソーステーブルのデータブロックをターゲットテーブルに移動していると考えられます。

検証の条件

テーブル定義

ソーステーブルとターゲットテーブル定義は同じ、ソートキーは lo_orderdate とします。

CREATE TABLE public.lineorder_target 
(
  lo_orderkey          INTEGER NOT NULL,
  lo_linenumber        INTEGER NOT NULL,
  lo_custkey           INTEGER NOT NULL,
  lo_partkey           INTEGER NOT NULL,
  lo_suppkey           INTEGER NOT NULL,
  lo_orderdate         INTEGER NOT NULL,
  lo_orderpriority     VARCHAR(15) NOT NULL,
  lo_shippriority      VARCHAR(1) NOT NULL,
  lo_quantity          INTEGER NOT NULL,
  lo_extendedprice     INTEGER NOT NULL,
  lo_ordertotalprice   INTEGER NOT NULL,
  lo_discount          INTEGER NOT NULL,
  lo_revenue           INTEGER NOT NULL,
  lo_supplycost        INTEGER NOT NULL,
  lo_tax               INTEGER NOT NULL,
  lo_commitdate        INTEGER NOT NULL,
  lo_shipmode          VARCHAR(10) NOT NULL
)
distkey(lo_partkey)
sortkey(lo_orderdate);

テスト用データ

db=> INSERT INTO public.lineorder_1992 SELECT * FROM public.lineorder WHERE lo_orderdate BETWEEN 19920000 AND 19929999;
INSERT 0 11402480
Time: 12061.933 ms
db=> INSERT INTO public.lineorder_1993 SELECT * FROM public.lineorder WHERE lo_orderdate BETWEEN 19930000 AND 19939999;
INSERT 0 11379006
Time: 12843.286 ms

[従来] ソート済みデータをディープコピー（INSERT INTO SELECT）

空のテーブルにソート済みデータをディープコピー

INSERT INTO SELECT でソート済みデータをディープコピーすると、ターゲットテーブル(lineorder_target)のデータはソート済みで格納されますので、VACUUMは不要です。ANALYZEの実行と、ソーステーブルのTRUNCATEを実施します。 データのディープコピーには12.878秒、データのソートは不要、統計情報の更新は3.806秒、ソーステーブルのデータ削除は0.423秒で、全て含めると17.107秒でした。

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 0_not_sorted | lineorder_target   |           0 |        0 |           0.000
 1_sorted     | lineorder_1992     |    11402480 | 11402480 |           1.000

db=> INSERT INTO public.lineorder_target SELECT * FROM public.lineorder_1992;
INSERT 0 11402480
Time: 12878.085 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 1_sorted     | lineorder_1992     |    11402480 | 11402480 |           1.000

db=> ANALYZE public.lineorder_target;
ANALYZE
Time: 3806.338 ms

db=> TRUNCATE TABLE public.lineorder_1992;
TRUNCATE TABLE and COMMIT TRANSACTION
Time: 423.389 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000

更にテーブルにソート済みデータをディープコピーで追加

ターゲットテーブル(lineorder_target)へ更にINSERT INTO SELECT でソート済みデータをディープコピーします。ターゲットテーブル(lineorder_target)のデータは未ソートで格納されますので、VACUUMは必要です。ANALYZEの実行と、ソーステーブルのTRUNCATEを実施します。 データのディープコピーには12.717秒、データのソートは28.269秒、統計情報の更新は2.108秒、ソーステーブルのデータ削除は0.879秒で、全て含めると43.973秒でした。

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 1_sorted     | lineorder_1993     |    11379006 | 11379006 |           1.000 

db=> INSERT INTO public.lineorder_target SELECT * FROM public.lineorder_1993;
INSERT 0 11379006
Time: 12717.126 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 22781486 |           0.501
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 1_sorted     | lineorder_1993     |    11379006 | 11379006 |           1.000

db=> VACUUM public.lineorder_target;
VACUUM
Time: 28269.610 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    22781486 | 22781486 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 1_sorted     | lineorder_1993     |    11379006 | 11379006 |           1.000

db=> ANALYZE public.lineorder_target;
ANALYZE
Time: 2108.294 ms

db=> TRUNCATE TABLE public.lineorder_1993;
TRUNCATE TABLE and COMMIT TRANSACTION
Time: 879.741 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    22781486 | 22781486 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 0_not_sorted | lineorder_1993     |           0 |        0 |           0.000

[新] ソート済みデータを移動（ALTER TABLE APPEND）

空のテーブルにソート済みデータを移動

ALTER TABLE APPEND でソート済みデータを移動すると、ターゲットテーブル(lineorder_target)のデータは未ソートで格納されますので、VACUUMが必要です。ANALYZEの実行を実施します。データは移動するのでソーステーブルのデータ削除は不要です。 データの移動には1.211秒でしたが、データのソートは47.653秒、統計情報の更新は1.021秒で、全て含めると49.855秒でした。 データの移動は高速ですが、データのソート（VACUUM）はかなり時間がかかってしまいました。

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 0_not_sorted | lineorder_target   |           0 |        0 |           0.000
 1_sorted     | lineorder_1992     |    11402480 | 11402480 |           1.000

db=> ALTER TABLE public.lineorder_target APPEND FROM public.lineorder_1992;
INFO:  ALTER TABLE APPEND "lineorder_target" from "lineorder_1992" is complete.
ALTER TABLE APPEND and COMMIT TRANSACTION
Time: 1211.296 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 0_not_sorted | lineorder_target   |           0 | 11402480 |           0.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000

db=> VACUUM public.lineorder_target;
VACUUM
Time: 47653.109 ms

db=> ANALYZE public.lineorder_target;
ANALYZE
Time: 1021.992 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000

更にテーブルにソート済みデータを移動

ターゲットテーブル(lineorder_target)へ更にALTER TABLE APPEND でソート済みデータを移動します。先程と同様にターゲットテーブル(lineorder_target)のデータは未ソートで格納されますので、VACUUMが必要です。ANALYZEの実行を実施します。データは移動するのでソーステーブルのデータ削除は不要です。 データの移動には1.344秒でしたが、データのソートは27.653秒、統計情報の更新は1.602秒で、全て含めると30.589秒でした。 データのソート（VACUUM）は空のテーブルに移動させた後のVACUUMの半分の時間で済ました。

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 1_sorted     | lineorder_1993     |    11379006 | 11379006 |           1.000 

db=> ALTER TABLE public.lineorder_target APPEND FROM public.lineorder_1993;
INFO:  ALTER TABLE APPEND "lineorder_target" from "lineorder_1993" is complete.
ALTER TABLE APPEND and COMMIT TRANSACTION
Time: 1344.405 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 22781486 |           0.501
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 0_not_sorted | lineorder_1993     |           0 |        0 |           0.000

db=> VACUUM public.lineorder_target;
VACUUM
Time: 27653.198 ms

db=> ANALYZE public.lineorder_target;
ANALYZE
Time: 1602.358 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    22781486 | 22781486 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 0_not_sorted | lineorder_1993     |           0 |        0 |           0.000

検証の結果

検証結果を以下の表にまとめました。ALTER TABLE APPENDは、データの「移動」については速いのですが、ソート済みデータを移動しても未ソートデータとして格納されるため、ソートキー指定したテーブルではデータ移動後にVACUUMが必要となります。

  • [従来] ソート済みデータをディープコピー（INSERT INTO SELECT）
データ ディープコピー時間 VACUUM時間 ANALYZE時間 TRUNCATE時間 合計時間
1992 12.878秒 (初回は不要) 3.806秒 0.423秒 17.107秒
1993 12.717秒 28.269秒 2.108秒 0.879秒 43.973秒
  • [新] ソート済みデータを移動（ALTER TABLE APPEND）
データ データ移動時間 VACUUM時間 ANALYZE時間 TRUNCATE時間 合計時間
1992 1.211秒 47.653秒 1.021秒 (不要) 49.855秒
1993 1.344秒 27.653秒 1.602秒 (不要) 30.589秒

最後に

ALTER TABLE APPENDでソート済みデータを移動することで、データブロックやゾーンマップのマージができたら画期的だと思いましたが、残念ながら現状は「移動」のようです。 といっても検証の結果から、ディープコピー（INSERT INTO SELECT）とデータの移動（ALTER TABLE APPEND）を組合せることでデータコピーが約３割ほど速くなることがわかりました。

以下の方針に従い、データコピーの方式の見直しをご検討ください。

  • 空のテーブルに対するデータコピーは従来通り、ディープコピー（INSERT INTO SELECT）
  • データが既に入っているテーブルに対するデータコピーは、データの移動（ALTER TABLE APPEND）

UPSERTでワーキングテーブルにコピー・作成した結果をファクトテーブルにコピーするといったユースケースでは、このテクニックが使えます。

Share this article

facebook logohatena logotwitter logo

関連記事

「AWSにおける生成AI＆データの全体像」というタイトルで 第8回 Classmethod AI Talks(CATs)に登壇しました #catalks

「AWSにおける生成AI＆データの全体像」というタイトルで 第8回 Classmethod AI Talks(CATs)に登壇しました #catalks

User avatar
石川覚
2024.11.21
[アップデート] Amazon Redshift デフォルトの動作変更によりセキュリティを強化

[アップデート] Amazon Redshift デフォルトの動作変更によりセキュリティを強化

User avatar
石川覚
2024.11.20
Amazon Redshift 分散スタイルのテーブル最適化を有効化するビューを作成してみた

Amazon Redshift 分散スタイルのテーブル最適化を有効化するビューを作成してみた

User avatar
石川覚
2024.11.15
Amazon Redshift ソートキーのテーブル最適化を有効化するビューを作成してみた

Amazon Redshift ソートキーのテーブル最適化を有効化するビューを作成してみた

User avatar
石川覚
2024.11.14
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.